package util;

import jxl.Cell;
import jxl.Sheet;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class ExcelUtils {

	 /**
     * 创建excel文档
     *
     * @param excelPath
     * @return
     */
    public static boolean createExcelFile(String excelPath) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        return outputHSSFWorkbook(workbook, excelPath);
    }


    private Workbook wb;


//    /**
//     * 读取Excel文件,取得表格内容
//     *
//     * @param fileName 文件路径
//     * @return List<String>
//     */
//    public static List<String> readExcel(String fileName) {
//        boolean isE2007 = false; // 判断是否是excel2007格式
//        List<String> list = new ArrayList<String>();
//        if (fileName.endsWith("xlsx"))
//            isE2007 = true;
//        try {
//            InputStream input = new FileInputStream(fileName); // 建立输入流
//            wb = null;
//            // 根据文件格式(2003或者2007)来初始化
//            if (isE2007)
//                wb = new XSSFWorkbook(input);
//            else
//                wb = new HSSFWorkbook(input);
//            org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); // 获得第一个表单
//            Iterator<Row> rows = sheet.rowIterator(); // 获得第一个表单的迭代器
//            while (rows.hasNext()) {
//                Row row = rows.next(); // 获得行数据
//                // System.out.println("Row #" + row.getRowNum()); //获得行号从0开始
//                Iterator<org.apache.poi.ss.usermodel.Cell> cells = row.cellIterator(); // 获得第一行的迭代器
//                while (cells.hasNext()) {
//                    org.apache.poi.ss.usermodel.Cell cell = cells.next();
//                    if (cell.getRowIndex() > 1) {
//                        list.add(cell.getStringCellValue());
//                    }
//                    /**
//                     * System.out.println("Cell #" + cell.getColumnIndex());
//                     * switch (cell.getCellType()) { //根据cell中的类型来输出数据 case
//                     * HSSFCell.CELL_TYPE_NUMERIC:
//                     * System.out.println(cell.getNumericCellValue()); break;
//                     * case HSSFCell.CELL_TYPE_STRING:
//                     * System.out.println(cell.getStringCellValue()); break;
//                     * case HSSFCell.CELL_TYPE_BOOLEAN:
//                     * System.out.println(cell.getBooleanCellValue()); break;
//                     * case HSSFCell.CELL_TYPE_FORMULA:
//                     * System.out.println(cell.getCellFormula()); break;
//                     * default: System.out.println("unsuported sell type");
//                     * break; }
//                     */
//                }
//            }
//        } catch (IOException ex) {
//            ex.printStackTrace();
//        }
//
//        return list;
//    }

    /**
     * 读取Excel文件,取得表格所有内容
     *
     * @param fileName 文件路径
     * @return List<String>
     */
    public static List<List<Object>> readExcelAll(String fileName, Integer rowIndex) {
        boolean isE2007 = false; // 判断是否是excel2007格式
        List<List<Object>> dataList = new ArrayList<List<Object>>();
        if (fileName.endsWith("xlsx"))
            isE2007 = true;
        try {
            InputStream input = new FileInputStream(fileName); // 建立输入流
            Workbook wb = null;
            // 根据文件格式(2003或者2007)来初始化
            if (isE2007)
                wb = new XSSFWorkbook(input);
            else
                wb = new HSSFWorkbook(input);
            org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); // 获得第一个表单
            Iterator<Row> rows = sheet.rowIterator(); // 获得第一个表单的迭代器

            while (rows.hasNext()) {
                Row row = rows.next(); // 获得行数据
                // System.out.println("Row #" + row.getRowNum()); //获得行号从0开始
                Iterator<org.apache.poi.ss.usermodel.Cell> cells = row.cellIterator(); // 获得第一行的迭代器
                List<Object> list = new ArrayList<Object>();
                while (cells.hasNext()) {
                    org.apache.poi.ss.usermodel.Cell cell = cells.next();
                    if (cell.getRowIndex() >= rowIndex) {
                        switch (cell.getCellType()) { // 根据cell中的类型来输出数据
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                //如果是日期格式,对格式进行强制转换
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    list.add(sdf.format(cell.getDateCellValue()));
                                } else {
                                    list.add(cell.getNumericCellValue());
                                }
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                list.add(cell.getStringCellValue());
                                break;
                            default:
                                list.add("null");
                                break;
                        }
                    }
                }
                if (list != null && list.size() > 0) {
                    dataList.add(list);
                }
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }

        return dataList;
    }

    /**
     * 读取Excel文件,取得表格所有内容
     *
     * @param fileName 文件路径
     * @return List<String>
     */
    public static List<List<String>> readExcelAllNew(String fileName) {
        boolean isE2007 = false; // 判断是否是excel2007格式
        List<List<String>> dataList = new ArrayList<List<String>>();
        if (fileName.endsWith("xlsx"))
            isE2007 = true;
        try {
            InputStream input = new FileInputStream(fileName); // 建立输入流
            Workbook wb = null;
            // 根据文件格式(2003或者2007)来初始化
            if (isE2007)
                wb = new XSSFWorkbook(input);
            else
                wb = new HSSFWorkbook(input);
            org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); // 获得第一个表单
            Iterator<Row> rows = sheet.rowIterator(); // 获得第一个表单的迭代器

            while (rows.hasNext()) {
                Row row = rows.next(); // 获得行数据
                List<String> list = new ArrayList<String>();
                // System.out.println("Row #" + row.getRowNum()); //获得行号从0开始
                Iterator<org.apache.poi.ss.usermodel.Cell> cells = row.cellIterator(); // 获得第一行的迭代器
                while (cells.hasNext()) {
                    org.apache.poi.ss.usermodel.Cell cell = cells.next();
                    if (cell.getRowIndex() >= 1) {
                        switch (cell.getCellType()) { // 根据cell中的类型来输出数据
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                list.add(String.valueOf((int) cell.getNumericCellValue()));
                                System.out.println(cell.getNumericCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                list.add(cell.getStringCellValue());
                                // System.out.println(cell.getStringCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                // System.out.println(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA:
                                // System.out.println(cell.getCellFormula());
                                break;
                            default:
                                list.add("null");
                                break;
                        }
                    }
                }
                dataList.add(list);
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }

        return dataList;
    }

    private static Workbook getWorkbook(String fileName) {
        boolean isE2007 = false; // 判断是否是excel2007格式
        if (fileName.endsWith("xlsx"))
            isE2007 = true;
        InputStream input;
        Workbook wb = null;
        try {
            input = new FileInputStream(fileName);
            if (isE2007)
                wb = new XSSFWorkbook(input);
            else
                wb = new HSSFWorkbook(input);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        // 根据文件格式(2003或者2007)来初始化
        catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 根据分割操作,取得数字
     *
     * @param content
     * @param regex
     * @return
     */
    public static Integer getPageNum(String content, String regex) {
        int i = 0;
        Pattern p = Pattern.compile(regex);
        Matcher num = p.matcher(content);
        if (num.find()) {
            String value = num.group(0);
            Pattern newP = Pattern.compile("\\d+");
            Matcher m = newP.matcher(value);
            if (m.find()) {
                i = Integer.parseInt(m.group(0));
            }

        }
        return i;
    }

    public static String getReplaceString(String str, String regx) {
        String result = "";
        Pattern p = Pattern.compile(regx);
        Matcher m = p.matcher(str);
        if (m.find()) {
            result = m.group(0);
        }
        return result;
    }


//    public static List<String[]> readExcel(File excelFile, int rowNum) throws BiffException, IOException {
//
//		// 创建一个list用来存读取的内容
//		List<String[]> list = new ArrayList<String[]>();
//		Workbook rwb = null;
//		Cell cell = null;
//
//		// 创建输入流
//		InputStream stream = new FileInputStream(excelFile);
//		// 获取Excel文件对象
//		rwb = Workbook.getWorkbook(stream);
//		// 获取文件的指定工作表 默认的第一个
//		Sheet sheet = rwb.getSheet(0);
//		// 行数(表头的目录不需要，从1开始)
//		for (int i = rowNum - 1; i < sheet.getRows(); i++) {
//			// 创建一个数组 用来存储每一列的值
//			String[] str = new String[sheet.getColumns()];
//			// 列数
//			for (int j = 0; j < sheet.getColumns(); j++) {
//				// 获取第i行，第j列的值
//				cell = sheet.getCell(j, i);
//				str[j] = cell.getContents();
//			}
//			list.add(str);
//		}
//		return list;
//	}

    /**
     * 插入新的工作表
     *
     * @param excelPath
     * @param sheetName
     * @return
     */
    public static boolean insertSheet(String excelPath, String sheetName) {
        try {
            if (FileUtils.checkFile(excelPath)) {
                HSSFWorkbook workbook = ExcelUtils.getHSSFWorkbook(excelPath);
                return outputHSSFWorkbook(workbook, excelPath);
            } else {
                System.out.println(excelPath + ":文件不存在.....");
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 复制工作表
     *
     * @param excelPath
     * @param sheetName
     * @param formSheetNum 从0开始
     * @return
     */
    public static boolean copySheet(String excelPath, String sheetName,
                                    int formSheetNum) {
        try {
            if (FileUtils.checkFile(excelPath)) {
                HSSFWorkbook workbook = ExcelUtils.getHSSFWorkbook(excelPath);
                if (!ExcelUtils.checkSheet(workbook, sheetName)) {
                    workbook.cloneSheet(formSheetNum);
                    workbook.setSheetName(workbook.getNumberOfSheets() - 1,
                            sheetName);
                    return outputHSSFWorkbook(workbook, excelPath);
                } else {
                    System.out.println(excelPath + ":存在同名工作表" + sheetName
                            + ".....");
                }
            } else {
                System.out.println(excelPath + ":文件不存在.....");
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 插入或者更新单元格
     *
     * @param excelPath
     * @param sheetName
     * @param rowNum
     * @param cellNum
     * @param value
     * @return
     */
    public static boolean insertOrUpdateCell(String excelPath,
                                             String sheetName, int rowNum, int cellNum, String value) {
        try {
            if (FileUtils.checkFile(excelPath)) {
                HSSFWorkbook wb = getHSSFWorkbook(excelPath);
                if (ExcelUtils.checkSheet(wb, sheetName)) {
                    HSSFSheet sheet = wb.getSheet(sheetName);
                    HSSFRow row = null;
                    if (sheet.getLastRowNum() < rowNum) {
                        row = sheet.createRow(rowNum);
                    } else {
                        row = sheet.getRow(rowNum);
                    }
                    //如果为空则创建
                    if (null==row) {
                        row = sheet.createRow(rowNum);
                   }
                    HSSFCell cell = row.getCell(cellNum);
                  //如果为空则创建
                    if(null==cell){
                    	cell = row.createCell(cellNum);
                    }
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(value);

                    return outputHSSFWorkbook(wb, excelPath);
                } else {
                    System.out.println(excelPath + "的" + sheetName
                            + ":工作表不存在.....");
                }
            } else {
                System.out.println(excelPath + ":文件不存在.....");
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 插入或者更新单元格
     *
     * @param excelPath
     * @param sheetIndex
     * @param rowNum
     * @param cellNum
     * @param value
     * @return
     */
    public static boolean insertOrUpdateCell(String excelPath, int sheetIndex,
                                             int rowNum, int cellNum, String value) {
        try {
            if (FileUtils.checkFile(excelPath)) {
                HSSFWorkbook wb = getHSSFWorkbook(excelPath);
                if (ExcelUtils.checkSheet(wb, sheetIndex)) {
                    HSSFSheet sheet = wb.getSheetAt(sheetIndex);
                    HSSFRow row = null;
                    if (sheet.getLastRowNum() < rowNum) {
                        row = sheet.createRow(rowNum);
                    } else {
                        row = sheet.getRow(rowNum);
                    }
                    if (null==row) {
                        row = sheet.createRow(rowNum);
                   }
                    HSSFCell cell = row.getCell(cellNum);
                    if(null==cell){
                    	cell = row.createCell(cellNum);
                    }
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(value);

                    return outputHSSFWorkbook(wb, excelPath);
                } else {
                    System.out.println(excelPath + "的" + sheetIndex
                            + ":工作表不存在.....");
                }
            } else {
                System.out.println(excelPath + ":文件不存在.....");
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 插入或者更新单元格，包含格式
     *
     * @param excelPath
     * @param sheetIndex
     * @param rowNum
     * @param cellNum
     * @param value
     * @param style
     * @return
     */
    public static boolean insertOrUpdateCell(String excelPath, int sheetIndex,
                                             int rowNum, int cellNum, String value, HSSFCellStyle style) {
        try {
            if (FileUtils.checkFile(excelPath)) {
                HSSFWorkbook wb = getHSSFWorkbook(excelPath);
                if (ExcelUtils.checkSheet(wb, sheetIndex)) {
                    HSSFSheet sheet = wb.getSheetAt(sheetIndex);
                    HSSFRow row = null;
                    if (sheet.getLastRowNum() < rowNum) {
                        row = sheet.createRow(rowNum);
                    } else {
                        row = sheet.getRow(rowNum);
                    }
                    if(null==row){
                    	row = sheet.createRow(rowNum);
                    }
                    HSSFCell cell = row.createCell(cellNum);
                    // cell.setCellType(HSSFCell.CELL_TYPE_STRING);

                    HSSFFont font = wb.createFont();
                    font.setFontHeight((short) 18);

                    HSSFCellStyle style1 = wb.createCellStyle();
                    style1.setFont(font);
                    // cell.setCellStyle(style1);
                    cell.setCellValue(value);

                    System.out.println(value);
                    return outputHSSFWorkbook(wb, excelPath);
                } else {
                    System.out.println("insertOrUpdateCell:" + excelPath + "的"
                            + sheetIndex + ":工作表不存在.....");
                }
            } else {
                System.out.println(excelPath + ":文件不存在.....");
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 插入指定行数据
     *
     * @param excelPath
     * @param sheetName
     * @param rowNum
     * @param values
     * @return
     */
    public static boolean insertOrUpadateRowDatas(String excelPath,
                                                  String sheetName, int rowNum, String... values) {
        try {
            for (int i = 0; i < values.length; i++) {
                insertOrUpdateCell(excelPath, sheetName, rowNum, i, values[i]);
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 插入指定行数据
     *
     * @param excelPath
     * @param sheetName
     * @param rowNum
     * @param list
     * @return
     */
    public static boolean insertOrUpadateRowDatas(String excelPath,
                                                  String sheetName, int rowNum, List<String> list) {
        try {
            for (int i = 0; i < list.size(); i++) {
                insertOrUpdateCell(excelPath, sheetName, rowNum, i, list.get(i));
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 读入excel
     *
     * @param excelPath
     * @return
     * @throws Exception
     */
    public static HSSFWorkbook getHSSFWorkbook(String excelPath)
            throws Exception {
        FileInputStream fs = null;
        try {
            fs = new FileInputStream(excelPath);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            return wb;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (fs != null)
                    fs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 输出Excel
     *
     * @param wb
     * @param excelPath
     * @return
     */
    private static boolean outputHSSFWorkbook(HSSFWorkbook wb, String excelPath) {
        FileOutputStream fOut = null;
        try {
            fOut = new FileOutputStream(excelPath);
            wb.write(fOut);
            fOut.flush();
            System.out.println(excelPath + ":文件生成...");
            return true;
        } catch (FileNotFoundException e) {
            // TODO 自动生成 catch 块
            e.printStackTrace();
        } catch (IOException e) {
            // TODO 自动生成 catch 块
            e.printStackTrace();
        } finally {
            try {
                if (fOut != null)
                    fOut.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;

    }

    /**
     * 检查是否存在工作表
     *
     * @param excelPath
     * @param sheetName
     * @return
     */
    public static boolean checkSheet(HSSFWorkbook wb, String sheetName) {
        try {
            for (int numSheets = 0; numSheets < wb.getNumberOfSheets(); numSheets++) {
                HSSFSheet sheet = wb.getSheetAt(numSheets);
                if (sheetName.equals(sheet.getSheetName())) {
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 检查是否存在sheet数量
     *
     * @param wb
     * @param sheetIndex
     * @return
     */
    public static boolean checkSheet(HSSFWorkbook wb, int sheetIndex) {
        try {
            if (wb.getNumberOfSheets() > sheetIndex)
                return true;
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 清除指定excel的工作表所有内容
     *
     * @param excelPath
     * @param sheet
     * @return
     */
    public static boolean cleanExcelFile(String excelPath, String sheetName) {
        try {
            if (FileUtils.checkFile(excelPath)) {
                HSSFWorkbook wb = getHSSFWorkbook(excelPath);
                if (ExcelUtils.checkSheet(wb, sheetName)) {
                    HSSFSheet sheet = wb.getSheet(sheetName);
                    for (int i = 0; i < sheet.getLastRowNum() + 1; i++) {
                        if (sheet.getRow(i) != null)
                            sheet.removeRow(sheet.getRow(i));
                    }
                    return outputHSSFWorkbook(wb, excelPath);
                } else {
                    System.out.println("cleanExcelFile:" + excelPath + "的"
                            + sheetName + ":工作表不存在.....");
                }

            } else {
                System.out.println(excelPath + ":文件不存在.....");
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 获取指定工作簿行数
     *
     * @param excelPath
     * @param sheetName
     * @return
     */
    public static int getExcelSheetRowNum(String excelPath, String sheetName) {
        try {
            if (FileUtils.checkFile(excelPath)) {
                HSSFWorkbook wb = getHSSFWorkbook(excelPath);
                if (ExcelUtils.checkSheet(wb, sheetName)) {
                    HSSFSheet sheet = wb.getSheet(sheetName);
                    return sheet.getLastRowNum() + 1;
                } else {
                    System.out.println("getExcelSheetRowNum:" + excelPath + "的"
                            + sheetName + ":工作表不存在.....");
                }

            } else {
                System.out.println(excelPath + ":文件不存在.....");
            }
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 删除行
     *
     * @excelPath
     * @param sheetName
     * @param row
     * @return
     */
    public static boolean deleteRow(String excelPath, String sheetName, int row) {
        if (getExcelSheetRowNum(excelPath, sheetName) > row) {
            try {
                if (FileUtils.checkFile(excelPath)) {
                    HSSFWorkbook wb = getHSSFWorkbook(excelPath);
                    if (ExcelUtils.checkSheet(wb, sheetName)) {
                        HSSFSheet sheet = wb.getSheet(sheetName);
                        if (sheet.getRow(row) != null)
                            sheet.removeRow(sheet.getRow(row));
                        return outputHSSFWorkbook(wb, excelPath);
                    } else {
                        System.out.println("deleteRow:" + excelPath + "的"
                                + sheetName + ":工作表不存在.....");
                    }

                } else {
                    System.out.println(excelPath + ":文件不存在.....");
                }
                return false;
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("不存在指定行");
            return true;
        }
        return false;
    }

}
